package dashboard.service;

import dashboard.model.ParamMaster;
import dashboard.model.RealTimeStatResult;
import dashboard.utils.FileUtil;

/**
 * 
 * @author xu guoneng
 *
 */
public class RealTimeDbStatService extends DatabaseService  {
	
	public RealTimeDbStatService(){
		SIMPLE_SQL_RESULT_OUT_FILE = "stored_script.html";
	}
	
	public RealTimeStatResult getSharedPoolStat(){
		
		String sql = "SELECT (1 - trunc((P2.value) / (P1.value), 4)) * 100  \"Execute to Parse %\" " + 
				   " FROM v$sysstat P1,  v$sysstat P2" + 
				   " WHERE P1.name = 'parse count (total)' " +
				   " AND P2.name = 'parse count (hard)'";
		
		double indicator = getJdbcTemplate().queryForObject(sql, Double.class);
		
		String command = FileUtil.readSqlScript("DB_SHARED_POOL.sql");
		String html = executeSqlCommandHtml(command);
		
		RealTimeStatResult result = new RealTimeStatResult(indicator, "Execute to Parse %", html);
		
		if(indicator <= getParamMaster().getSpAttn()){
			result.setSuggestion("Please increase the value of parameter '" + "shared_pool_size" + "' in init.ora");
		}
		
		return result;
	}
	
	public RealTimeStatResult getBufferCacheStat(){
		String sql = "SELECT (1 - trunc((P3.value) / (P1.value + P2.value), 4)) * 100  \"Buffer Cache Hit Ratio %\" " + 
				   " FROM v$sysstat P1,  v$sysstat P2,  v$sysstat P3 " + 
				   " WHERE P1.name = 'db block gets from cache' " +
				   " AND P2.name = 'consistent gets from cache' " +
				   " AND P3.name = 'physical reads cache'";
		
		double indicator = getJdbcTemplate().queryForObject(sql, Double.class);
		
		String command = FileUtil.readSqlScript("DB_CACHE_ADVICE.sql");
		String html = executeSqlCommandHtml(command);
		
		RealTimeStatResult result = new RealTimeStatResult(indicator, "Buffer Cache Hit Ratio %", html);
		
		if(indicator <= getParamMaster().getBcAttn()){
			result.setSuggestion("Please increase the value of parameter '" + "db_cache_size" + "' in init.ora");
		}
		
		return result;
	}
	
	public RealTimeStatResult getRedoLogBufferStat(){
		String sql = "SELECT (1 - trunc((P2.value) / P1.value, 4)) * 100  \"Redo Nowait Ratio %\" " +
					 " FROM v$sysstat P1,  v$sysstat P2 " +
					 " WHERE P1.name = 'redo entries' AND P2.name = 'redo log space requests'";
		
		double indicator = getJdbcTemplate().queryForObject(sql, Double.class);
		
		String command = FileUtil.readSqlScript("REDO_LOG_BUFFER.sql");
		String html = executeSqlCommandHtml(command);

		RealTimeStatResult result = new RealTimeStatResult(indicator, "Redo Nowait Ratio %", html);
		
		if(indicator <= getParamMaster().getRdAttn()){
			result.setSuggestion("Please increase the value of parameter '" + "log_buffer" + "' in init.ora");
		}
		
		return result;
	}
	
	public RealTimeStatResult getSortMemStat(){
		
		String sql = "SELECT (1 - trunc((P2.value + P3.value) / (P1.value), 4)) * 100  \"In-memory Sort %\" " + 
				   " FROM v$sysstat P1,  v$sysstat P2,  v$sysstat P3 " + 
				   " WHERE P1.name = 'sorts (rows)' " +
				   " AND P2.name = 'sorts (memory)' " +
				   " AND P3.name = 'sorts (disk)'";
		
		double indicator = getJdbcTemplate().queryForObject(sql, Double.class);
		
		String command = FileUtil.readSqlScript("DB_SORT_MEM.sql");
		String html = executeSqlCommandHtml(command);
		
		RealTimeStatResult result = new RealTimeStatResult(indicator, "In-memory Sort %", html);
		
		if(indicator <= getParamMaster().getSmAttn()){
			result.setSuggestion("Please increase the value of parameter 'sort_area_size' in init.ora");
		}
		
		return result;
	}
	
	protected String executeSqlCommandHtml(String command){
		String fileName = "temp_cmd_" + new java.util.Date().getTime() + ".sql";
		
		String formattedcommand = "set linesize 9999\r\n"
								+ "set pagesize 50000\r\n"
								+ "set verify off\r\n"
								+ "set echo off\r\n"
								+ "set feedback off\r\n"
								+ "set trimspool on\r\n"
								+ "set termout on\r\n"
								+ "SET MARKUP HTML ON\r\n"
								+ "spool " + getResultOutputFilePath() + "\r\n"
							    + "\r\n" + command.trim() + "\r\n/ \r\n"
							    + "EXIT\r\n/ ";
		
		FileUtil.writeFile(fileName, formattedcommand);
		String result = executeSqlScript(FileUtil.FILE_DIR + fileName);
		FileUtil.deleteFile(FileUtil.FILE_DIR + fileName);
		
		return result;
	}
}
